import sqlite3
import os
from datetime import datetime

class DBOperations:
    def __init__(self):
        self.db_path = 'equipment.db'
        self.conn = None
        
    def connect(self):
        self.conn = sqlite3.connect(self.db_path)
        return self.conn.cursor()
        
    def close(self):
        if self.conn:
            self.conn.commit()
            self.conn.close()
            self.conn = None
    
    # 用户认证
    def authenticate_user(self, username, password):
        c = self.connect()
        c.execute("SELECT * FROM users WHERE username=? AND password=?", 
                 (username, password))
        user = c.fetchone()
        self.close()
        return user is not None
    
    # 设备入库
    def add_equipment(self, data):
        c = self.connect()
        try:
            c.execute("""INSERT INTO equipment 
                          (date, sn, model, manufacturer, network_element, 
                           type, status, standard, photo_path) 
                          VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)""", 
                     (datetime.now().strftime('%Y-%m-%d'), 
                      data['sn'], data.get('model', ''), data['manufacturer'], 
                      data['network_element'], data['type'], '在库', 
                      data['standard'], data['photo_path']))
            return True
        except sqlite3.IntegrityError:
            return False
        finally:
            self.close()
    
    # 更新设备状态
    def update_equipment_status(self, sn, status):
        """更新设备状态
        
        Args:
            sn: 设备SN码
            status: 新状态
            
        Returns:
            bool: 是否成功
        """
        try:
            conn = sqlite3.connect(self.db_path)
            c = conn.cursor()
            
            # 检查设备是否存在且状态为"在库"
            c.execute("SELECT status FROM equipment WHERE sn = ?", (sn,))
            result = c.fetchone()
            
            if not result:
                return False
            
            if result[0] != '在库':
                return False
            
            # 更新设备状态
            c.execute("UPDATE equipment SET status = ? WHERE sn = ?", (status, sn))
            conn.commit()
            return True
            
        except Exception as e:
            print(f"Error in update_equipment_status: {str(e)}")
            return False
            
        finally:
            conn.close()
    
    # 设备出库
    def outbound_equipment(self, sn):
        c = self.connect()
        # 检查设备当前状态是否为'在库'
        c.execute("SELECT status, network_element FROM equipment WHERE sn=?", (sn,))
        result = c.fetchone()
        
        if not result or result[0] != '在库':
            self.close()
            return False
            
        # 更新设备状态、出库时间和网元信息
        c.execute("""UPDATE equipment 
                    SET status='出库', 
                        outbound_time=?, 
                        original_network_element=?,
                        current_network_element=NULL
                    WHERE sn=?""", 
                 (datetime.now().strftime('%Y-%m-%d %H:%M:%S'),
                  result[1],  # 保存原始网元名称
                  sn))
        affected = c.rowcount
        self.close()
        return affected > 0
    
    # 设备销库
    def discard_equipment(self, sn, current_network_element=None):
        """销库设备
        
        Args:
            sn: 设备SN码
            current_network_element: 当前网元名称
            
        Returns:
            bool: 是否成功
        """
        try:
            conn = sqlite3.connect('equipment.db')
            c = conn.cursor()
            
            # 检查设备是否存在且状态为"出库"
            c.execute("SELECT status FROM equipment WHERE sn = ?", (sn,))
            result = c.fetchone()
            
            if not result:
                return False
            
            if result[0] != '出库':
                return False
            
            # 更新设备状态为"销库"并记录销库时间和当前网元名称
            c.execute("""
                UPDATE equipment 
                SET status = '销库',
                    discard_time = ?,
                    current_network_element = ?
                WHERE sn = ?
            """, (datetime.now().strftime('%Y-%m-%d %H:%M:%S'), 
                  current_network_element, sn))
            
            conn.commit()
            return True
        
        except Exception as e:
            print(f"Error in discard_equipment: {str(e)}")
            return False
        
        finally:
            conn.close()
    
    # 批量销库
    def batch_discard(self, sn_list, current_network_element=None):
        """批量销库设备
        
        Args:
            sn_list: 设备SN码列表
            current_network_element: 当前网元名称
            
        Returns:
            int: 成功销库的设备数量
        """
        try:
            conn = sqlite3.connect('equipment.db')
            c = conn.cursor()
            count = 0
            current_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
            
            for sn in sn_list:
                # 检查设备是否存在且状态为"出库"
                c.execute("SELECT status FROM equipment WHERE sn = ?", (sn,))
                result = c.fetchone()
                
                if result and result[0] == '出库':
                    c.execute("""
                        UPDATE equipment 
                        SET status = '销库',
                            discard_time = ?,
                            current_network_element = ?
                        WHERE sn = ?
                    """, (current_time, current_network_element, sn))
                    count += c.rowcount
            
            conn.commit()
            return count
            
        except Exception as e:
            print(f"Error in batch_discard: {str(e)}")
            return 0
            
        finally:
            conn.close()
    
    # 查询设备
    def get_equipment(self, sn=None):
        c = self.connect()
        if sn:
            c.execute("""SELECT 
                            id, date, sn, model, manufacturer, 
                            network_element, type, 
                            status, standard, photo_path,
                            outbound_time, discard_time,
                            original_network_element, current_network_element
                          FROM equipment WHERE sn=?""", (sn,))
        else:
            c.execute("""SELECT 
                            id, date, sn, model, manufacturer, 
                            network_element, type, 
                            status, standard, photo_path,
                            outbound_time, discard_time,
                            original_network_element, current_network_element
                          FROM equipment""")
        result = c.fetchall()
        self.close()
        return result
    
    # 统计查询
    def get_statistics(self, query, params=None):
        """获取统计数据
        
        Args:
            query: SQL查询语句
            params: 查询参数列表
            
        Returns:
            list: 统计数据列表
        """
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                
                if params:
                    cursor.execute(query, params)
                else:
                    cursor.execute(query)
                    
                columns = [description[0] for description in cursor.description]
                results = []
                
                for row in cursor.fetchall():
                    result = {}
                    for i, column in enumerate(columns):
                        result[column] = row[i]
                    results.append(result)
                    
                return results
                
        except Exception as e:
            print(f"Error in get_statistics: {str(e)}")
            raise
    
    # 删除设备记录
    def delete_equipment(self, sn):
        c = self.connect()
        try:
            c.execute("DELETE FROM equipment WHERE sn=?", (sn,))
            return c.rowcount > 0
        finally:
            self.close()
    
    def search_equipment(self, manufacturer=None, type=None, standard=None, model=None):
        """根据条件搜索设备"""
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                query = """
                    SELECT sn, manufacturer, type, standard, network_element, 
                           date, status,
                           CASE 
                               WHEN status = '在库' THEN network_element
                               ELSE original_network_element 
                           END as original_network_element,
                           current_network_element, outbound_time, discard_time
                    FROM equipment
                    WHERE 1=1
                """
                params = []
                
                if manufacturer:
                    query += " AND manufacturer = ?"
                    params.append(manufacturer)
                if type:
                    query += " AND type = ?"
                    params.append(type)
                if standard:
                    query += " AND standard = ?"
                    params.append(standard)
                if model:
                    query += " AND model = ?"
                    params.append(model)
                
                query += " ORDER BY date DESC"
                
                cursor.execute(query, params)
                columns = [description[0] for description in cursor.description]
                return [dict(zip(columns, row)) for row in cursor.fetchall()]
        except Exception as e:
            print(f"Error searching equipment: {str(e)}")
            return []
            
    def get_inventory(self):
        """获取所有设备信息"""
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                cursor.execute("""
                    SELECT sn, manufacturer, type, standard, network_element, 
                           date, status,
                           CASE 
                               WHEN status = '在库' THEN network_element
                               ELSE original_network_element 
                           END as original_network_element,
                           current_network_element, outbound_time, discard_time
                    FROM equipment
                    ORDER BY date DESC
                """)
                columns = [description[0] for description in cursor.description]
                return [dict(zip(columns, row)) for row in cursor.fetchall()]
        except Exception as e:
            print(f"Error getting inventory: {str(e)}")
            return []

    def get_unique_manufacturers(self):
        """获取所有唯一的生产厂商"""
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                cursor.execute("SELECT DISTINCT manufacturer FROM equipment ORDER BY manufacturer")
                return [row[0] for row in cursor.fetchall()]
        except Exception as e:
            print(f"Error getting unique manufacturers: {str(e)}")
            return []

    def get_unique_types(self):
        """获取所有唯一的设备类型"""
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                cursor.execute("SELECT DISTINCT type FROM equipment ORDER BY type")
                return [row[0] for row in cursor.fetchall()]
        except Exception as e:
            print(f"Error getting unique types: {str(e)}")
            return []

    def get_unique_standards(self):
        """获取所有唯一的制式"""
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                cursor.execute("SELECT DISTINCT standard FROM equipment ORDER BY standard")
                return [row[0] for row in cursor.fetchall()]
        except Exception as e:
            print(f"Error getting unique standards: {str(e)}")
            return []
            
    def batch_delete_equipment(self, sn_list):
        """批量删除设备
        
        Args:
            sn_list: 设备SN码列表
            
        Returns:
            int: 成功删除的设备数量
        """
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                count = 0
                for sn in sn_list:
                    cursor.execute("DELETE FROM equipment WHERE sn=?", (sn,))
                    count += cursor.rowcount
                conn.commit()
                return count
        except Exception as e:
            print(f"Error in batch_delete_equipment: {str(e)}")
            return 0

    def get_connection(self):
        return sqlite3.connect(self.db_path)
    
    def update_equipment_status_and_network(self, sn, new_status, new_network_element):
        """更新设备状态和网元名称"""
        try:
            conn = self.get_connection()
            c = conn.cursor()
            
            # 检查设备是否存在且状态为"出库"
            c.execute('SELECT status FROM equipment WHERE sn = ?', (sn,))
            result = c.fetchone()
            
            if not result:
                return False
                
            current_status = result[0]
            if current_status != '出库':
                return False
            
            # 更新设备状态和网元名称
            c.execute('''UPDATE equipment 
                        SET status = ?, network_element = ?
                        WHERE sn = ?''', (new_status, new_network_element, sn))
            
            conn.commit()
            return True
            
        except Exception as e:
            print(f"更新设备状态和网元名称失败: {str(e)}")
            return False
        finally:
            conn.close()